from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from sql import MySQLDB
from myexcel import *
from mytools import *
from config import *
import tkinter.messagebox as messagebox
import openpyxl,concurrent.futures
import time,os,subprocess,uuid

class NYZFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='无验证页采集', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        TMGGFrame(self).pack(pady=5,fill=X,padx=5)


class TMGGFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)

        self.session=requests.Session()
        self.createWidgets()
    
    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='条码公告采集', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='第几页开始采集:').pack(side=LEFT, padx=2, pady=2)
        self.FirstPageIpt = Entry(f00,width=5)
        self.FirstPageIpt.pack(side=LEFT, padx=2, pady=2)
        self.FirstPageIpt.insert(0,'1')
        Label(f00, text='最大采集页数:').pack(side=LEFT, padx=2, pady=2)
        self.MaxPageIpt = Entry(f00,width=7)
        self.MaxPageIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f00, text='每份文件最大行数:').pack(side=LEFT, padx=2, pady=2)
        self.MaxRowInput= Entry(f00,width=10)
        self.MaxRowInput.pack(side=LEFT, padx=2, pady=2)
        self.MaxRowInput.insert(0,1000000)

        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)
        Label(f01, text='采集模式:').pack(side=LEFT, padx=2, pady=2)
        self.CJModeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.CJModeSelect['value'] = ['注册','注销']
        self.CJModeSelect.pack(side=LEFT, padx=5, pady=2)
        self.CJModeSelect.current(0)

        f02=Frame(f1)
        f02.pack(fill=X,padx=2,pady=2)
        self.btn_cj=Button(f02,text='开始采集',command=lambda:Thread(target=self.begin_cj).start(),fg='#ffffff',bg='green')
        self.btn_cj.pack(side=LEFT,padx=2, pady=2)


    def begin_cj(self):
        db = MySQLDB(DB_PATH)
        # 列定义
        columns_to_add = {
            'code': 'VARCHAR(255)',
            'loginDate': 'DATE',
            'logDate':'DATE',
            'logoutFlag':'INT',
            'fid': 'VARCHAR(255)'
        }
        # 添加列（如果不存在）
        db.add_columns_if_not_exists('CompanyName', columns_to_add)
        # 关闭连接
        db.close_connection()

        page=self.FirstPageIpt.get().strip()
        maxpage=self.MaxPageIpt.get().strip()
        max_row=self.MaxRowInput.get().strip()
        if not page or not maxpage or not max_row:
            messagebox.showinfo('提示','请先设置采集页数')
            return
        self.btn_cj.config(state=DISABLED)
        session=self.session
        headers={
            'Host':'dynamic.gs1cn.org',
            'Origin':'http://www.ancc.org.cn',
            'Token':'null',
            'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36'
        }
        page=int(page)
        maxpage=int(maxpage)
        max_row =int(max_row)
        logoutFlag=self.CJModeSelect.current()
        j=0

        cjdatas=[]
        while True:
            data={
            'limit':500,
            'logoutFlag' :logoutFlag,
            'page': page,

            }

            try:
                res=session.post('http://dynamic.gs1cn.org/office/eanupc/api/getEanUpcByCondition',json=data,headers=headers,timeout=30)
                jsondata=res.json()

                if jsondata['msg']=='success':
                    curdata=jsondata['data']
                    if j==0:
                        totalPage=curdata['totalPage']
                        print(f'共{totalPage}页')
                    currPage=curdata['currPage']
                    if page>totalPage or page>maxpage:
                        break
                    
                    plist=jsondata['data']['list']
                    db = MySQLDB(DB_PATH)
                    aff=0
                    for item in plist:
                        zdstr=','.join(['code','loginDate','logDate','logoutFlag','name','fid'])
                        params=[item['code'],item['loginDate'],item['logDate'],item['logoutFlag'],item['firmName'],item['fid']]
                        wstr=','.join([r'%s' for _ in params])
                        sqlstr=f'insert into {TABLE_NAME_CHANG_SHANG}({zdstr}) values({wstr})'
                        aff+=db.execute_command(sqlstr,params)
                        cjdatas.append(params)

                    print(f'第{page}页,《{self.CJModeSelect.get()}》采集到 {len(plist)} 个公司,存入数据库:{aff}个')
                    db.close_connection()
                if currPage ==totalPage:
                    break
                page+=1
                j+=1
            except Exception as e:

                print(f'第{page}页,《{self.CJModeSelect.get()}》,出现错误 =>{e}')
        

        print(f'本次共采集《{self.CJModeSelect.get()}》,公司数量:{len(cjdatas)}')
        
        total_records = len(cjdatas)
        batch_number = 0
        dirpath=f'采集结果_{getTimeStr()}'
        os.mkdir(dirpath)
        for i in range(0, total_records, max_row-1):
            batch_data = cjdatas[i:i + max_row-1]
            
            # 创建一个新的工作簿
            workbook = openpyxl.Workbook()
            sheet = workbook.active
            sheet.append(['code','loginDate','logDate','条码类型(logoutFlag)','公司名称(firmName)','fid'])
            # 将批次数据写入工作簿
            for row in batch_data:
                sheet.append(row)
            
            # 为每个批次生成文件名
            file_name = f'{dirpath}/条码_batch_{batch_number + 1}.xlsx'
            
            # 保存到 Excel 文件
            workbook.save(file_name)            
            batch_number += 1


        print(f'《{self.CJModeSelect.get()}》,共采集{j}页,成功保存到目录:《{dirpath}》')
        messagebox.showinfo('提示',f'《{self.CJModeSelect.get()}》,共采集{j}页,成功保存到目录:《{dirpath}》')
        self.btn_cj.config(state=NORMAL)

            

# import tkinter
# list_win = tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('主页')
# NYZFrame(list_win).pack(fill=X, padx=3, pady=3)
# list_win.mainloop()